﻿/*
Deployment script for GateShift_Schema
*/

GO
SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON;

SET NUMERIC_ROUNDABORT OFF;

GO
PRINT N'Dropping dbo.DF_Accounts_AccountID...';


GO
ALTER TABLE [dbo].[Accounts] DROP CONSTRAINT [DF_Accounts_AccountID];


GO
PRINT N'Dropping dbo.FK_Characters_Accounts...';


GO
ALTER TABLE [dbo].[Characters] DROP CONSTRAINT [FK_Characters_Accounts];

GO
PRINT N'Starting rebuilding table dbo.Accounts...';


GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SET XACT_ABORT ON;


GO
BEGIN TRANSACTION;

CREATE TABLE [dbo].[tmp_ms_xx_Accounts] (
    [AccountID]                  UNIQUEIDENTIFIER CONSTRAINT [DF_Accounts_AccountID] DEFAULT (newid()) ROWGUIDCOL NOT NULL,
    [AccountName]                VARCHAR (50)     NOT NULL,
    [Password]                   VARBINARY (256)  NOT NULL,
    [PasswordSalt]               VARBINARY (256)  NOT NULL,
    [EmailAddress]               VARCHAR (256)    NOT NULL,
    [Locked]                     BIT              NOT NULL,
    [DateCreated]                DATETIME         NOT NULL,
    [DateLastLogin]              DATETIME         NULL,
    [DateLastLocked]             DATETIME         NULL,
    [DateLastPasswordChanged]    DATETIME         NULL,
    [RecoveryQuestion]           VARCHAR (256)    NULL,
    [RecoveryAnswer]             VARCHAR (128)    NULL,
    [FailedPasswordAttemptCount] TINYINT          CONSTRAINT [DF_Accounts_FailedPasswordAttemptCount] DEFAULT ((0)) NOT NULL,
    [DateLockedUntil]            DATETIME         NULL,
    [DateLastBanned]             DATETIME         NULL,
    [Banned]                     BIT              CONSTRAINT [DF_Accounts_Banned] DEFAULT ((0)) NOT NULL,
    [DateBannedUntil]            DATETIME         NULL,
    [Comment]                    TEXT             NULL
);

ALTER TABLE [dbo].[tmp_ms_xx_Accounts]
    ADD CONSTRAINT [tmp_ms_xx_clusteredindex_PK_Accounts] PRIMARY KEY CLUSTERED ([AccountID] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);

IF EXISTS (SELECT TOP 1 1
           FROM   [dbo].[Accounts])
    BEGIN
        INSERT INTO [dbo].[tmp_ms_xx_Accounts] ([AccountID], [AccountName], [Password], [PasswordSalt], [EmailAddress], [Locked], [DateCreated], [DateLastLogin], [DateLastLocked], [Comment])
        SELECT   [AccountID],
                 [AccountName],
                 [Password],
                 [PasswordSalt],
                 [EmailAddress],
                 [Locked],
                 [DateCreated],
                 [DateLastLogin],
                 [DateLastLocked],
                 [Comment]
        FROM     [dbo].[Accounts]
        ORDER BY [AccountID] ASC;
    END

DROP TABLE [dbo].[Accounts];

EXECUTE sp_rename N'[dbo].[tmp_ms_xx_Accounts]', N'Accounts';

EXECUTE sp_rename N'[dbo].[tmp_ms_xx_clusteredindex_PK_Accounts]', N'PK_Accounts', N'OBJECT';

COMMIT TRANSACTION;


GO
PRINT N'Creating dbo.Version...';


GO
CREATE TABLE [dbo].[Version] (
    [VersionID]        TINYINT      IDENTITY (1, 1) NOT NULL,
    [FeatureName]      VARCHAR (20) NOT NULL,
    [FeatureChangeset] SMALLINT     NOT NULL
);


GO
PRINT N'Creating dbo.PK_Version...';


GO
ALTER TABLE [dbo].[Version]
    ADD CONSTRAINT [PK_Version] PRIMARY KEY CLUSTERED ([VersionID] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);


GO
PRINT N'Creating dbo.FK_Characters_Accounts...';


GO
ALTER TABLE [dbo].[Characters]
    ADD CONSTRAINT [FK_Characters_Accounts] FOREIGN KEY ([AccountID]) REFERENCES [dbo].[Accounts] ([AccountID]) ON DELETE NO ACTION ON UPDATE NO ACTION;
